﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Threading.Tasks;
using FangChanCrm.Services.Interface;
using FangChanCrmEF;
using FangChanCrmModel;
using Maticsoft.Model;
using Microsoft.EntityFrameworkCore;
using Nancy.Routing.Trie;
using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Bcpg.OpenPgp;

namespace FangChanCrm.Services.Realization
{
    public class t_KeHuBiaoService : It_KeHuBiaoService
    {
        public readonly MyContext _Context;
        public t_KeHuBiaoService(MyContext myContext)
        {
            _Context = myContext;
        }

        public t_KeHuBiao Add(t_KeHuBiao t_KeHuBiao)
        {
            //var trans = _Context.Database.BeginTransaction();
            //try
            //{
            //    _Context.SaveChanges();
            //    trans.Commit();
            //}
            //catch (Exception)
            //{
            //    trans.Rollback();
            //}
            //finally
            //{
            //    trans.Dispose();
            //}
            //trans.Dispose();



            t_KeHuBiao.BeiZhu1 = DateTime.Now.ToString();
            _Context.t_KeHuBiao.Add(t_KeHuBiao);
            _Context.SaveChanges();
            var KeHuId = _Context.t_KeHuBiao.FirstOrDefault(x => x.BeiZhu1 == t_KeHuBiao.BeiZhu1 && x.BeiZhu2 == t_KeHuBiao.BeiZhu2 && x.BeiZhu3 == t_KeHuBiao.BeiZhu3 && x.BeiZhu4 == t_KeHuBiao.BeiZhu4 && x.BeiZhu5 == t_KeHuBiao.BeiZhu5 && x.BeiZhu6 == t_KeHuBiao.BeiZhu6 && x.BeiZhu7 == t_KeHuBiao.BeiZhu7 && x.BeiZhu8 == t_KeHuBiao.BeiZhu8 && x.BeiZhu9 == t_KeHuBiao.BeiZhu9 && x.BeiZhu10 == t_KeHuBiao.BeiZhu10 && x.KeHuXingMing == t_KeHuBiao.KeHuXingMing && x.ShouJiHaoMa == t_KeHuBiao.ShouJiHaoMa && x.XingBie == t_KeHuBiao.XingBie && x.NianLing == t_KeHuBiao.NianLing && x.XingBie == t_KeHuBiao.XingBie && x.YiXiangDengJi == t_KeHuBiao.YiXiangDengJi && x.YiXiangDanJia == t_KeHuBiao.YiXiangDanJia && x.YiXiangZongJia == t_KeHuBiao.YiXiangZongJia && x.RenYuanId == t_KeHuBiao.RenYuanId && x.YiXiangHuXing == t_KeHuBiao.YiXiangHuXing && x.YiXiangLouCeng == t_KeHuBiao.YiXiangLouCeng && x.YiXiangMianJi == t_KeHuBiao.YiXiangMianJi && x.YiXiangTaoShu == t_KeHuBiao.YiXiangTaoShu && x.JiaTingJieGou == t_KeHuBiao.JiaTingJieGou && x.JiaoYuChengDu == t_KeHuBiao.JiaoYuChengDu && x.HunYinZhuangTai == t_KeHuBiao.HunYinZhuangTai && x.ZhengJianHaoMa == t_KeHuBiao.ZhengJianHaoMa);
            t_XiaoXiTiXingBiao t_XiaoXiTiXingBiaoBaoBei = new t_XiaoXiTiXingBiao();
            t_XiaoXiTiXingBiaoBaoBei.KeHuId = KeHuId.Id;
            t_XiaoXiTiXingBiaoBaoBei.TiXingLeiXing = "报备";
            t_XiaoXiTiXingBiaoBaoBei.ShengYuTiXingCiShu = 10;
            t_XiaoXiTiXingBiaoBaoBei.TiXingShiJian = System.DateTime.Now.AddHours(72).ToString();
            t_XiaoXiTiXingBiaoBaoBei.YeWuYuanId = t_KeHuBiao.RenYuanId;
            _Context.t_XiaoXiTiXingBiao.Add(t_XiaoXiTiXingBiaoBaoBei);
            _Context.SaveChanges();
            t_XiaoXiTiXingBiao t_XiaoXiTiXingBiaoGenJin = new t_XiaoXiTiXingBiao();
            t_XiaoXiTiXingBiaoGenJin.KeHuId = KeHuId.Id;
            t_XiaoXiTiXingBiaoGenJin.TiXingLeiXing = "跟进";
            t_XiaoXiTiXingBiaoGenJin.ShengYuTiXingCiShu = 15;
            t_XiaoXiTiXingBiaoGenJin.TiXingShiJian = System.DateTime.Now.AddHours(72).ToString();
            t_XiaoXiTiXingBiaoGenJin.YeWuYuanId = t_KeHuBiao.RenYuanId;
            _Context.t_XiaoXiTiXingBiao.Add(t_XiaoXiTiXingBiaoGenJin);
            _Context.SaveChanges();
            return KeHuId;
        }

        public t_KeHuBiao Add2(t_KeHuBiao t_KeHuBiao)
        {
            t_KeHuBiao.BeiZhu1 = DateTime.Now.ToString();
            _Context.t_KeHuBiao.Add(t_KeHuBiao);
            _Context.SaveChanges();
            var KeHuId = _Context.t_KeHuBiao.FirstOrDefault(x => x.BeiZhu1 == t_KeHuBiao.BeiZhu1 && x.BeiZhu2 == t_KeHuBiao.BeiZhu2 && x.BeiZhu3 == t_KeHuBiao.BeiZhu3 && x.BeiZhu4 == t_KeHuBiao.BeiZhu4 && x.BeiZhu5 == t_KeHuBiao.BeiZhu5 && x.BeiZhu6 == t_KeHuBiao.BeiZhu6 && x.BeiZhu7 == t_KeHuBiao.BeiZhu7 && x.BeiZhu8 == t_KeHuBiao.BeiZhu8 && x.BeiZhu9 == t_KeHuBiao.BeiZhu9 && x.BeiZhu10 == t_KeHuBiao.BeiZhu10 && x.KeHuXingMing == t_KeHuBiao.KeHuXingMing && x.ShouJiHaoMa == t_KeHuBiao.ShouJiHaoMa && x.XingBie == t_KeHuBiao.XingBie && x.NianLing == t_KeHuBiao.NianLing && x.XingBie == t_KeHuBiao.XingBie && x.YiXiangDengJi == t_KeHuBiao.YiXiangDengJi && x.YiXiangDanJia == t_KeHuBiao.YiXiangDanJia && x.YiXiangZongJia == t_KeHuBiao.YiXiangZongJia && x.RenYuanId == t_KeHuBiao.RenYuanId && x.YiXiangHuXing == t_KeHuBiao.YiXiangHuXing && x.YiXiangLouCeng == t_KeHuBiao.YiXiangLouCeng && x.YiXiangMianJi == t_KeHuBiao.YiXiangMianJi && x.YiXiangTaoShu == t_KeHuBiao.YiXiangTaoShu && x.JiaTingJieGou == t_KeHuBiao.JiaTingJieGou && x.JiaoYuChengDu == t_KeHuBiao.JiaoYuChengDu && x.HunYinZhuangTai == t_KeHuBiao.HunYinZhuangTai && x.ZhengJianHaoMa == t_KeHuBiao.ZhengJianHaoMa);
            return KeHuId;
        }

        public bool Delete(int id)
        {
            _Context.t_KeHuBiao.Remove(_Context.t_KeHuBiao.Where(x => x.Id == id).ToList()[0]);
            return _Context.SaveChanges() > 0;
        }

        public t_KeHuBiao GetById(int id)
        {
            return _Context.t_KeHuBiao.FirstOrDefault(x => x.Id == id);
        }

        public List<t_KeHuBiao> GetList()
        {
            //return _Context.t_KeHuBiao.Include(x => x.t_XiaoShouBiao).Include(x => x.t_RenYuanBiao).Include(x => x.t_RenYuanBiao.t_GangWeiBiao).ToList();
            return _Context.t_KeHuBiao.Include(x => x.t_XiaoShouBiao).ToList();
        }

        public string ReturnWhere(t_KeHuBiao t_KeHuBiao)
        {
            string returnstr = " RenYuanId !=1 ";
            string tiaojian = "";
            if (t_KeHuBiao.XingBie != null)//客户查询条件
            {
                if (t_KeHuBiao.KeHuXingMing != null)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " or KeHuXingMing.Contains(\"" + t_KeHuBiao.KeHuXingMing + "\")";
                    }
                    else
                    {
                        tiaojian += " KeHuXingMing.Contains(\"" + t_KeHuBiao.KeHuXingMing + "\")";
                    }
                }
                if (t_KeHuBiao.ShouJiHaoMa != null)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " or ShouJiHaoMa.Contains(\"" + t_KeHuBiao.ShouJiHaoMa + "\")";
                    }
                    else
                    {
                        tiaojian += " ShouJiHaoMa.Contains(\"" + t_KeHuBiao.ShouJiHaoMa + "\")";
                    }
                }
                if (t_KeHuBiao.ZhengJianHaoMa != null)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " or ZhengJianHaoMa.Contains(\"" + t_KeHuBiao.ZhengJianHaoMa + "\")";
                    }
                    else
                    {
                        tiaojian += " ZhengJianHaoMa.Contains(\"" + t_KeHuBiao.ZhengJianHaoMa + "\")";
                    }
                }
                if (t_KeHuBiao.YiXiangDengJi != null)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " or YiXiangDengJi.Contains(\"" + t_KeHuBiao.YiXiangDengJi + "\")";
                    }
                    else
                    {
                        tiaojian += " YiXiangDengJi.Contains(\"" + t_KeHuBiao.YiXiangDengJi + "\")";
                    }
                }
                if (t_KeHuBiao.XingBie != null)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " or XingBie.Contains(\"" + t_KeHuBiao.XingBie + "\")";
                    }
                    else
                    {
                        tiaojian += " XingBie.Contains(\"" + t_KeHuBiao.XingBie + "\")";
                    }

                }
                if (t_KeHuBiao.LianMingKeHu != null)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " or LianMingKeHu.Contains(\"" + t_KeHuBiao.LianMingKeHu + "\")";
                    }
                    else
                    {
                        tiaojian += " LianMingKeHu.Contains(\"" + t_KeHuBiao.LianMingKeHu + "\")";
                    }
                }
                if (tiaojian != "")
                {
                    returnstr += " and (" + tiaojian + ")";
                }
            }
            else if (t_KeHuBiao.KeHuXingMing != null || t_KeHuBiao.Id != 0 || t_KeHuBiao.ShouJiHaoMa != null)//数据中心查询条件
            {
                if (t_KeHuBiao.Id != 0)
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " AND Id = " + t_KeHuBiao.Id + "";
                    }
                    else
                    {
                        tiaojian += " Id = " + t_KeHuBiao.Id + "";
                    }
                }
                if (t_KeHuBiao.KeHuXingMing != null || t_KeHuBiao.KeHuXingMing != "")
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " AND KeHuXingMing.Contains(\"" + t_KeHuBiao.KeHuXingMing + "\")";
                    }
                    else
                    {
                        tiaojian += " KeHuXingMing.Contains(\"" + t_KeHuBiao.KeHuXingMing + "\")";
                    }
                }
                if (t_KeHuBiao.ShouJiHaoMa != null || t_KeHuBiao.ShouJiHaoMa != "")
                {
                    if (tiaojian != "")
                    {
                        tiaojian += " AND ShouJiHaoMa.Contains(\"" + t_KeHuBiao.ShouJiHaoMa + "\")";
                    }
                    else
                    {
                        tiaojian += " ShouJiHaoMa.Contains(\"" + t_KeHuBiao.ShouJiHaoMa + "\")";
                    }
                }
                if (tiaojian != "")
                {
                    returnstr += " and (" + tiaojian + ")";
                }
            }
            else
            {
                if (tiaojian != "")
                {
                    returnstr += " and (" + tiaojian + ")";
                }
            }
            return returnstr;
        }

        public List<t_KeHuBiao> huibangQiYelist(int id)
        {
            return _Context.t_KeHuBiao.Where(x => x.RenYuanId == id).ToList();
        }


        public string GetListWithFenYe(Controllers.KeHuController.biao _biao, int start, int limit, string dir, int column)
        {

            //string sql2 = "select zi.Id as Id, zi.KeHuXingMing as xingming, (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.YeWuYuanId) as yewuyuanxingming,  (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.ZhiYeGuWenId) as zhiyeguwenxingming, zi.ShouJiHaoMa as haoma,  t_XiaoShouXiangXiBiao.CaoZuoShiJian as shijian  from(SELECT *, ROW_NUMBER() OVER(ORDER BY Id ASC) AS Num FROM t_KeHuBiao)AS zi left join  t_XiaoShouBiao on zi.Id = t_XiaoShouBiao.KeHuId  left join  t_XiaoShouXiangXiBiao on t_XiaoShouBiao.Id = t_XiaoShouXiangXiBiao.XiaoShouBiaoId  where Num between " + start + " and " + (start + limit) + "  AND t_XiaoShouXiangXiBiao.CaoZuoShiJian = (SELECT MAX(t_XiaoShouXiangXiBiao.CaoZuoShiJian) from t_XiaoShouXiangXiBiao WHERE t_XiaoShouBiao.Id = t_XiaoShouXiangXiBiao.XiaoShouBiaoId)  ORDER BY zi.Id";
            //string sql2 = "select zi.Id as Id, zi.KeHuXingMing as xingming, (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.YeWuYuanId) as yewuyuanxingming,  (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.ZhiYeGuWenId) as zhiyeguwenxingming, zi.ShouJiHaoMa as haoma,  t_XiaoShouXiangXiBiao.CaoZuoShiJian as shijian  from(SELECT *, ROW_NUMBER() OVER(ORDER BY Id ASC) AS Num FROM t_KeHuBiao)AS zi left join  t_XiaoShouBiao on zi.Id = t_XiaoShouBiao.KeHuId  left join  t_XiaoShouXiangXiBiao on t_XiaoShouBiao.Id = t_XiaoShouXiangXiBiao.XiaoShouBiaoId  where t_XiaoShouXiangXiBiao.CaoZuoShiJian = (SELECT MAX(t_XiaoShouXiangXiBiao.CaoZuoShiJian) from t_XiaoShouXiangXiBiao WHERE t_XiaoShouBiao.Id = t_XiaoShouXiangXiBiao.XiaoShouBiaoId)  ORDER BY zi.Id";
            //var list1 = _Context.Database.SqlQuery<biao>(sql2).ToList();

            string zhengdaoxu = "";//第一组sql  语句排序
            string zhengdaoxu2 = "";//第二组sql  语句排序

            switch (column)
            {
                case 0:
                    if (dir == "asc")
                    {
                        zhengdaoxu = " zui.xingming asc";
                        zhengdaoxu2 = " quan.xingming asc";
                    }
                    else
                    {
                        zhengdaoxu = " zui.xingming desc";
                        zhengdaoxu2 = " quan.xingming desc";
                    }
                    break;
                case 1:
                    if (dir == "asc")
                    {
                        zhengdaoxu = " zui.yewuyuanxingming asc";
                        zhengdaoxu2 = " quan.yewuyuanxingming asc";
                    }
                    else
                    {
                        zhengdaoxu = " zui.yewuyuanxingming desc";
                        zhengdaoxu2 = " quan.yewuyuanxingming desc";
                    }
                    break;
                case 2:
                    if (dir == "asc")
                    {
                        zhengdaoxu = " zui.zhiyeguwenxingming asc";
                        zhengdaoxu2 = " quan.zhiyeguwenxingming asc";
                    }
                    else
                    {
                        zhengdaoxu = " zui.zhiyeguwenxingming  desc";
                        zhengdaoxu2 = " quan.zhiyeguwenxingming  desc";
                    }
                    break;
                case 3:
                    if (dir == "asc")
                    {
                        zhengdaoxu = " zui.haoma asc";
                        zhengdaoxu2 = " quan.haoma asc";
                    }
                    else
                    {
                        zhengdaoxu = " haoma desc";
                        zhengdaoxu2 = " quan.haoma desc";
                    }
                    break;
                case 4:
                    if (dir == "asc")
                    {
                        zhengdaoxu = " zui.shijian asc";
                        zhengdaoxu2 = " quan.shijian asc";
                    }
                    else
                    {
                        zhengdaoxu = " zui.shijian desc";
                        zhengdaoxu2 = " quan.shijian desc";
                    }
                    break;
                default:
                    break;
            }


            //.Skip(start).Take(limit).ToList()


            var zong = 0;//总数 变量
            List<Controllers.KeHuController.biao> biao = new List<Controllers.KeHuController.biao>();//条件
            //判断是否有条件
            if (_biao.xingming != "" || _biao.yewuyuanxingming != "" || _biao.zhiyeguwenxingming != "" || _biao.haoma != "" || _biao.shijian != "" || _biao.shijian2 != "")
            {
                string pin = "";//拼接条件

                if (_biao.xingming != "")//姓名条件是否为空
                {
                    if (pin != "")//判断拼接 条件是否加and
                    {
                        pin += " and xingming = '" + _biao.xingming + "'";
                    }
                    else
                    {
                        pin += " xingming = '" + _biao.xingming + "'";
                    }
                }
                if (_biao.yewuyuanxingming != "")//业务员姓名条件是否为空
                {
                    if (pin != "")//判断拼接 条件是否加and
                    {
                        pin += " and yewuyuanxingming = '" + _biao.yewuyuanxingming + "'";
                    }
                    else
                    {
                        pin += " yewuyuanxingming = '" + _biao.yewuyuanxingming + "'";
                    }
                }
                if (_biao.zhiyeguwenxingming != "")//置业顾问姓名条件是否为空
                {
                    if (pin != "")//判断拼接 条件是否加and
                    {
                        pin += " and zhiyeguwenxingming = '" + _biao.zhiyeguwenxingming + "'";
                    }
                    else
                    {
                        pin += " zhiyeguwenxingming = '" + _biao.zhiyeguwenxingming + "'";

                    }
                }
                if (_biao.haoma != "")//号码条件是否为空
                {
                    if (pin != "")//判断拼接 条件是否加and
                    {
                        pin += " and haoma = '" + _biao.haoma + "'";
                    }
                    else
                    {
                        pin += " haoma = '" + _biao.haoma + "'";
                    }
                }
                if (_biao.shijian != "" && _biao.shijian2 != "")//范围时间条件是否为空
                {
                    if (pin != "")//判断拼接 条件是否加and
                    {
                        pin += " and dateadd(day,0,shijian) > '" + Convert.ToDateTime(_biao.shijian).ToString("yyyy-MM-dd HH:mm:ss") + "' and dateadd(day,0,shijian) < '" + Convert.ToDateTime(_biao.shijian2).ToString("yyyy-MM-dd 23:59:59") + "'";
                    }
                    else
                    {
                        pin += " dateadd(day,0,shijian) > '" + Convert.ToDateTime(_biao.shijian).ToString("yyyy-MM-dd HH:mm:ss") + "' and dateadd(day,0,shijian) < '" + Convert.ToDateTime(_biao.shijian2).ToString("yyyy-MM-dd 23:59:59") + "'";
                    }
                }
                else
                {
                    if (_biao.shijian != "")//开始时间条件是否为空
                    {
                        if (pin != "")//判断拼接 条件是否加and
                        {
                            pin += " and dateadd(day,0,shijian) > '" + Convert.ToDateTime(_biao.shijian).ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        }
                        else
                        {
                            pin += " dateadd(day,0,shijian) > '" + Convert.ToDateTime(_biao.shijian).ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        }
                    }
                    if (_biao.shijian2 != "")//结束时间时间条件是否为空
                    {
                        if (pin != "")//判断拼接 条件是否加and
                        {
                            pin += " and dateadd(day,0,shijian) < '" + Convert.ToDateTime(_biao.shijian2).ToString("yyyy-MM-dd 23:59:59") + "'";
                        }
                        else
                        {
                            pin += " dateadd(day,0,shijian) < '" + Convert.ToDateTime(_biao.shijian2).ToString("yyyy-MM-dd 23:59:59") + "'";
                        }
                    }
                }

                //转化sql语句为查询总数有条件
                string sql = "select count(*) FROM (select distinct zi.Id as Id, zi.KeHuXingMing as xingming, (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.YeWuYuanId) as yewuyuanxingming,(select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.ZhiYeGuWenId) as zhiyeguwenxingming,zi.ShouJiHaoMa as haoma, (select MAX(CaoZuoShiJian) from t_XiaoShouXiangXiBiao WHERE t_XiaoShouXiangXiBiao.XiaoShouBiaoId = t_XiaoShouBiao.Id) as shijian  from(SELECT *, ROW_NUMBER() OVER(ORDER BY Id ASC) AS Num FROM t_KeHuBiao)AS zi left join (select * from t_XiaoShouBiao as xiaoshou WHERE Id in (select MAX(xiaoshou2.Id) from t_XiaoShouBiao as xiaoshou2  group by xiaoshou2.KeHuId having count(xiaoshou2.KeHuId)>0)) as t_XiaoShouBiao on zi.Id = t_XiaoShouBiao.KeHuId) as quan WHERE " + pin + " group by quan.Id";
                //转化sql语句为查询全部有条件
                string sql2 = "select * from  (select row_number() over(order by  " + zhengdaoxu2 + ") as paixu,* FROM (select distinct zi.Id as Id, zi.KeHuXingMing as xingming, (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.YeWuYuanId) as yewuyuanxingming,(select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.ZhiYeGuWenId) as zhiyeguwenxingming,zi.ShouJiHaoMa as haoma, (select MAX(CaoZuoShiJian) from t_XiaoShouXiangXiBiao WHERE t_XiaoShouXiangXiBiao.XiaoShouBiaoId = t_XiaoShouBiao.Id) as shijian  from(SELECT *, ROW_NUMBER() OVER(ORDER BY Id ASC) AS Num FROM t_KeHuBiao)AS zi left join (select * from t_XiaoShouBiao as xiaoshou WHERE Id in (select MAX(xiaoshou2.Id) from t_XiaoShouBiao as xiaoshou2  group by xiaoshou2.KeHuId having count(xiaoshou2.KeHuId)>0)) as t_XiaoShouBiao on zi.Id = t_XiaoShouBiao.KeHuId) as quan  WHERE " + pin + " ) as quan2 WHERE quan2.paixu between " + (start + 1) + " and " + (start + limit) + "";
                if (_Context.Database.SqlQuery<Controllers.KeHuController.zong>(sql).Count() != 0)//判断是否有数据
                {
                    zong = _Context.Database.SqlQuery<Controllers.KeHuController.zong>(sql).ToList()[0].Id;//条件总数
                }
                else
                {
                    zong = 0;
                }
                var list2 = _Context.Database.SqlQuery<Controllers.KeHuController.biao>(sql2).ToList();//查询全部有条件
                biao = list2;//有条件
            }
            else
            {
                //总数sql语句
                string sql = "select Count(*) as Id from t_KeHuBiao";
                //查多少条到多少条数据 sql语句
                string sql2 = " select * from (select row_number() over (order by " + zhengdaoxu + ") as paixu,* from (select distinct zi.Id as Id, zi.KeHuXingMing as xingming, (select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.YeWuYuanId) as yewuyuanxingming,(select XingMing from t_RenYuanBiao WHERE t_RenYuanBiao.Id = t_XiaoShouBiao.ZhiYeGuWenId) as zhiyeguwenxingming,zi.ShouJiHaoMa as haoma, (select MAX(CaoZuoShiJian) from t_XiaoShouXiangXiBiao WHERE t_XiaoShouXiangXiBiao.XiaoShouBiaoId = t_XiaoShouBiao.Id) as shijian  from(SELECT *, ROW_NUMBER() OVER(ORDER BY Id ASC) AS Num FROM t_KeHuBiao)AS zi left join (select * from t_XiaoShouBiao as xiaoshou WHERE Id in (select MAX(xiaoshou2.Id) from t_XiaoShouBiao as xiaoshou2  group by xiaoshou2.KeHuId having count(xiaoshou2.KeHuId)>0)) as t_XiaoShouBiao on zi.Id = t_XiaoShouBiao.KeHuId ) as zui) as zui2 WHERE zui2.paixu between " + (start + 1) + " and " + (start + limit) + "";
                if (_Context.Database.SqlQuery<Controllers.KeHuController.zong>(sql).Count() != 0)//判断是否有数据
                {
                    zong = _Context.Database.SqlQuery<Controllers.KeHuController.zong>(sql).ToList()[0].Id;//总数
                }
                else
                {
                    zong = 0;
                }

                var list2 = _Context.Database.SqlQuery<Controllers.KeHuController.biao>(sql2).ToList();//查多少条到多少条数据

                biao = list2;//无条件
            }

            return Json(biao, limit, zong).ToString();//分页



            //return Json(biao, start, limit, zong).ToString();
            //return Helper.ListToJsonAddFenYe(returnstr, start, limit);
        }

        public string Json<T>(IList<T> KeHuBiao, int limit, int zong)
        {

            int shu = KeHuBiao.Count;
            int zuihoushu = limit - shu;
            //int xunhuanshu = 10;
            List<T> returnlist = new List<T>();
            //if (start + limit > shu)
            //{
            //    xunhuanshu = shu;
            //}
            //else
            //{
            //    xunhuanshu = start + limit;
            //}
            for (int i = 0; i < limit - zuihoushu; i++)
            {
                returnlist.Add(KeHuBiao[i]);
            }
            JsonSerializerSettings jsSettings = new JsonSerializerSettings
            {
                ReferenceLoopHandling = ReferenceLoopHandling.Ignore
            };
            string returnjson = JsonConvert.SerializeObject(returnlist, jsSettings);

            string jsonString = "{\"draw\":1,\"total\":" + zong + ",\"total\":" + zong + ",";
            jsonString += "\"" + "data" + "\":" + "" + returnjson + "" + ",";
            jsonString = jsonString.TrimEnd(',');
            jsonString += "}";

            return jsonString;
        }



        public string GetListWithFenYe2(t_KeHuBiao t_KeHuBiao, int start, int limit, string dir, int column)
        {
            var list = new List<t_KeHuBiao>();
            switch (column)
            {
                case 0:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.KeHuXingMing).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.KeHuXingMing).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 1:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.XingBie).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.XingBie).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 2:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.NianLing).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.NianLing).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 3:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.ZhuangTai).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.ZhuangTai).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 4:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.ShouJiHaoMa).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.ShouJiHaoMa).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 5:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.t_RenYuanBiao.XingMing).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.t_RenYuanBiao.XingMing).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 6:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.t_RenYuanBiao.t_GangWeiBiao.GangWeiMingCheng).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.t_RenYuanBiao.t_GangWeiBiao.GangWeiMingCheng).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 7:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.BuMenMingCheng).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.BuMenMingCheng).Skip(start).Take(limit).ToList();
                    }
                    break;
                case 8:
                    if (dir == "asc")
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.ZhanQuZhuangTai).Skip(start).Take(limit).ToList();
                    }
                    else
                    {
                        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.ZhanQuZhuangTai).Skip(start).Take(limit).ToList();
                    }
                    break;
                //case 9:
                //    if (dir == "asc")
                //    {
                //        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderBy(x => x.t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.t_GongSiBiao.GongSiMingCheng).Skip(start).Take(limit).ToList();
                //    }
                //    else
                //    {
                //        list = _Context.t_KeHuBiao.Include(x => x.t_RenYuanBiao).ThenInclude(x => x.t_GangWeiBiao).ThenInclude(x => x.t_BuMenBiao).ThenInclude(x => x.t_GongSiBiao).Where(ReturnWhere(t_KeHuBiao)).OrderByDescending(x => x.t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.t_GongSiBiao.GongSiMingCheng).Skip(start).Take(limit).ToList();
                //    }
                    //break;
                default:
                    break;
            }
            var cou = _Context.t_KeHuBiao.Where(ReturnWhere(t_KeHuBiao)).Count();
            for (int i = 0; i < list.Count; i++)
            {
                list[i].BeiZhu1 = null;
                list[i].GOngZuoDiZhi = null;
                list[i].GeRenBiaoQian = null;
                list[i].GongZuoQuYu = null;
                list[i].GuanZhuNeiRong = null;
                list[i].HunYinZhuangTai = null;
                list[i].JiaTingJieGou = null;
                list[i].JiaoYuChengDu = null;
                list[i].JuZhuDiZhi = null;
                list[i].JuZhuQuYu = null;
                list[i].KeHuBeiZhu = null;
                list[i].LianMingKeHu = null;
                list[i].RenZhiMeiTi = null;
                list[i].ShouRuQingKuang = null;

                int panduan = 0;
                if (list[i].YiXiangXinXi != "")
                {
                    panduan = Convert.ToInt32(list[i].YiXiangXinXi);//房源小区Id
                }
                if (_Context.t_FangYuanBiao.Where(x => x.Id == panduan).ToList().Count() != 0)
                {
                    list[i].YiXiangXinXi = _Context.t_FangYuanBiao.Where(x => x.Id == panduan).ToList()[0].XiaoQuMingCheng;
                }
                else
                {
                    list[i].YiXiangXinXi = "";
                }

                list[i].YiXiangDanJia = null;
                list[i].YiXiangHuXing = null;
                list[i].YiXiangLouCeng = null;
                list[i].YiXiangMianJi = null;
                list[i].YiXiangTaoShu = null;
                list[i].YiXiangYeTai = null;
                list[i].YiXiangYongTu = null;
                list[i].YiXiangZongJia = null;
                list[i].ZhengJianHaoMa = null;
                list[i].ZhengJianLeiXing = null;
                list[i].ZhiYeShenFen = null;
                if (list[i].t_RenYuanBiao.t_KeHuBiaos != null)
                {
                    list[i].t_RenYuanBiao.t_KeHuBiaos = null;
                }
                if (list[i].t_RenYuanBiao.t_GangWeiBiao.t_RenYuanBiaos != null)
                {
                    list[i].t_RenYuanBiao.t_GangWeiBiao.t_RenYuanBiaos = null;
                }
                if (list[i].t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.t_GangWeiBiaos != null)
                {
                    list[i].t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.t_GangWeiBiaos = null;
                }
                if (list[i].t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.t_GongSiBiao.t_BuMenBiaos != null)
                {
                    list[i].t_RenYuanBiao.t_GangWeiBiao.t_BuMenBiao.t_GongSiBiao.t_BuMenBiaos = null;
                }
            }
            return Helper.ListToJsonAddFenYeHYYSuperFast(list, cou);
        }

        public List<t_FangYuanKeHuGuanLianBiao> GetListfykh(int id)
        {
            return _Context.t_FangYuanKeHuGuanLianBiao.Where(x => x.KeHuId == id).ToList();
        }
    }
}
